<!-- build time:Tue Dec 17 2019 19:26:45 GMT+0800 (GMT+08:00) --><!doctype html><html class="theme-next mist" lang="zh-Hans"><head><meta name="generator" content="Hexo 3.8.0"><meta name="google-site-verification" content="7Tau9WyVgxnsEY9oYedu9g0U6_8akOX3wiKbaYcrg9A"><meta name="baidu-site-verification" content="EVwLiaxdxX"><link href="/css/xps13.css" rel="stylesheet" type="text/css"><link href="/css/message.css" rel="stylesheet" type="text/css"><script type="text/javascript" src="/js/jquery-1.11.3.min.js"></script><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css"><link href="/css/main.css?v=5.1.1" rel="stylesheet" type="text/css"><meta name="keywords" content="DataBase,Oracle,Oracle 11g,"><link rel="alternate" href="/atom.xml" title="MrBird" type="application/atom+xml"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.1"><meta name="description" content="PL/SQL(Procedural Language/SQL)是一种过程化语言，在PL/SQL 中可以通过IF 语句或LOOP 语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL 语言就能够实现操控程序处理的细节过程。PL/SQL 程序都是以块（BLOCK）为基本单位，整个PL/SQL 块分3 部分：声明部分（用DECLARE开头）、执行部分（以BEGIN"><meta name="keywords" content="DataBase,Oracle,Oracle 11g"><meta property="og:type" content="article"><meta property="og:title" content="Oracle PL&#x2F;SQL编程"><meta property="og:url" content="http://mrbird.cc/Oracle-PL-SQL编程.html"><meta property="og:site_name" content="MrBird"><meta property="og:description" content="PL/SQL(Procedural Language/SQL)是一种过程化语言，在PL/SQL 中可以通过IF 语句或LOOP 语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL 语言就能够实现操控程序处理的细节过程。PL/SQL 程序都是以块（BLOCK）为基本单位，整个PL/SQL 块分3 部分：声明部分（用DECLARE开头）、执行部分（以BEGIN"><meta property="og:locale" content="zh-Hans"><meta property="og:updated_time" content="2019-10-28T12:14:46.258Z"><meta name="twitter:card" content="summary"><meta name="twitter:title" content="Oracle PL&#x2F;SQL编程"><meta name="twitter:description" content="PL/SQL(Procedural Language/SQL)是一种过程化语言，在PL/SQL 中可以通过IF 语句或LOOP 语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL 语言就能够实现操控程序处理的细节过程。PL/SQL 程序都是以块（BLOCK）为基本单位，整个PL/SQL 块分3 部分：声明部分（用DECLARE开头）、执行部分（以BEGIN"><script type="text/javascript" id="hexo.configurations">var NexT=window.NexT||{},CONFIG={root:"/",scheme:"Mist",sidebar:{position:"left",display:"always",offset:12,offset_float:0,b2t:!1,scrollpercent:!1},fancybox:!1,motion:!1}</script><title>Oracle PL/SQL编程 | MrBird</title></head><body ondragstart="return!1" class="animsition" lang="zh-Hans" style="overflow-x:hidden;padding-right:280px"><script type="text/javascript" src="/js/mo.min.js"></script><style>@media (min-width:768px) and (max-width:991px){body .header-innerr{width:700px!important}}.header-innerr{margin:0 auto;padding:100px 0 70px;width:880px}@media (min-width:1600px){.container .header-innerr{width:1200px}}.header-innerr{position:relative}.header-innerr{padding:20px 0 0}.header-innerr:after,.header-innerr:before{content:" ";display:table}.header-innerr:after{clear:both}@media (max-width:767px){.header-innerr{width:auto;padding:10px;margin-bottom:-20px}}</style><div class="container sidebar-position-left page-post-detail"><div class="headband"></div><header id="header" class="header"><div class="header-inner"><div class="site-brand-wrapper"><div class="site-meta"><link href="https://fonts.font.im/css?family=Merienda" rel="stylesheet"><div class="custom-logo-site-title"><a href="/" class="brand" rel="start"><span class="logo-line-before"><i></i></span> <span class="site-title" style="font-family:Merienda;font-size:1.3rem">MrBird</span> <span class="logo-line-after"><i></i></span></a></div><p class="site-subtitle"></p></div><div class="site-nav-toggle"><button><span class="btn-bar"></span> <span class="btn-bar"></span> <span class="btn-bar"></span></button></div></div><nav class="site-nav"><ul id="menu" class="menu"><li class="menu-item menu-item-home"><a href="/" rel="section">HOME</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section">ARCHIVES</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section">TAGS</a></li><li class="menu-item menu-item-friends"><a href="/friends/" rel="section">FRIENDS</a></li><div class="sidebar-toggle" style="display:none"><div class="sidebar-toggle-line-wrap"><span class="sidebar-toggle-line sidebar-toggle-line-first"></span> <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span> <span class="sidebar-toggle-line sidebar-toggle-line-last"></span></div></div></ul><div class="site-search"><div class="popup search-popup local-search-popup"><div class="local-search-header clearfix"><span class="search-icon"><i class="fa fa-search"></i> </span><span class="popup-btn-close"><i class="fa fa-times-circle"></i></span><div class="local-search-input-wrapper"><input autocomplete="off" placeholder="Search" spellcheck="false" type="text" id="local-search-input"></div></div><div id="local-search-result"></div></div></div></nav></div><div class="header-innerr"><div class="note info" style="margin:0;letter-spacing:.15px">🐤手把手教你搭建<strong>Spring Cloud微服务权限系统</strong>（从零到部署）：<a style="color:#40dab2;font-weight:600" href="https://www.kancloud.cn/mrbird/spring-cloud" target="_blank">https://www.kancloud.cn/mrbird/spring-cloud</a></div></div></header><main id="main" class="main"><div class="main-inner"><div class="content-wrap"><div id="content" class="content"><div id="posts" class="posts-expand"><article class="post post-type-normal" itemscope itemtype="http://schema.org/Article"><link itemprop="mainEntityOfPage" href="http://mrbird.cc/Oracle-PL-SQL编程.html"><span hidden itemprop="author" itemscope itemtype="http://schema.org/Person"><meta itemprop="name" content="MrBird"><meta itemprop="description" content=""><meta itemprop="image" content="/images/blogImage.jpg"></span><span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization"><meta itemprop="name" content="MrBird"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">Oracle PL/SQL编程</h1><div class="post-meta"><span class="post-time"><span class="post-meta-item-icon"><i class="fa fa-calendar-o"></i> </span><span class="post-meta-item-text">Posted on</span> <time title="创建于" itemprop="dateCreated datePublished" datetime="2017-11-02T15:25:07+08:00">2017-11-02 </time></span><span></span> <span class="post-meta-divider">|</span> <span class="page-pv"><i class="fa fa-laptop"></i>&nbsp;&nbsp;Visit count <span class="busuanzi-value" id="busuanzi_value_page_pv"></span></span></div></header><div class="post-body" itemprop="articleBody"><p>PL/SQL(Procedural Language/SQL)是一种过程化语言，在PL/SQL 中可以通过IF 语句或LOOP 语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL 语言就能够实现操控程序处理的细节过程。</p><p>PL/SQL 程序都是以块（BLOCK）为基本单位，整个PL/SQL 块分3 部分：声明部分（用DECLARE开头）、执行部分（以BEGIN 开头）和异常处理部分（以EXCEPTION 开头）：<a id="more"></a></p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">[<span class="keyword">DECLARE</span>]</span><br><span class="line">   <span class="comment">--声明部分，可选</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">   <span class="comment">--执行部分，必须</span></span><br><span class="line">[<span class="keyword">EXCEPTION</span>]</span><br><span class="line">   <span class="comment">--异常处理部分，可选</span></span><br><span class="line"><span class="keyword">END</span></span><br></pre></td></tr></table></figure><p></p><p>对于 PL/SQL 块中的语句，需要指出的是：每一条语句都必须以分号结束，每条SQL 语句可以写成多行的形式，同样必须使用分号来结束。另外，一行中也可以有多条SQL 语句，但是它们之间必须以分号分隔。比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">    a int:=100;</span><br><span class="line">    b int:=200;</span><br><span class="line">    c number;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">    c:=(a+b)/(a-b);</span><br><span class="line">    dbms_output.put_line(c);</span><br><span class="line">  exception</span><br><span class="line">    when zero_divide then</span><br><span class="line">    dbms_output.put_line('除数不能为零');</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">-3</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><p>其中，“set serveroutput on”命令来实现在服务端显示执行结果；“dbms_output.put_line(c);”语句用于输出信息。</p><h2 id="数据类型"><a href="#数据类型" class="headerlink" title="数据类型"></a>数据类型</h2><h3 id="基本数据类型"><a href="#基本数据类型" class="headerlink" title="基本数据类型"></a>基本数据类型</h3><p><strong>1.数值类型</strong></p><p>数值类型主要包括NUMBER、PLS_INTEGER 和BINARY_INTEGER 3 种基本类型。其中，NUMBER 类型的变量可以存储整数或浮点数；而BINARY_INTEGER或PLS_INTEGER 类型的变量只存储整数。</p><p>NUMBER 类型还可以通过NUMBER(P,S)的形式来格式化数字，其中，参数P表示精度，参数S表示刻度范围。精度是指数值中所有有效数字的个数。比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">money number(9,2);</span><br></pre></td></tr></table></figure><p></p><p><strong>2.字符类型</strong></p><p>字符类型主要包括 VARCHAR2、CHAR、LONG、NCHAR 和NVARCHAR2 等。这些类型的变量用来存储字符串或字符数据。</p><ul><li><p>VARCHAR2 类型：用于存储可变长度的字符串，其语法格式为：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">VARCHAR2(maxlength)</span><br></pre></td></tr></table></figure><p>参数 maxlength表示可以存储字符串的最大长度，maxlength的最大值可以是32767 字节。</p></li><li><p>CHAR 类型：CHAR 类型表示指定长度的字符串，其语法格式如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CHAR(maxlength)</span><br></pre></td></tr></table></figure><p>参数 maxlength 是指可存储字符串的最大长度，以字节为单位，最大为32767字节，CHAR 类型的默认最大长度为1字节。</p></li><li><p>LONG 类型：LONG 类型表示一个可变的字符串，最大长度是32767 字节。</p></li><li><p>NCHAR 和NVARCHAR2类型：这两种数据类型的长度要根据各国字符集来确定，只能具体情况具体分析。</p></li></ul><p><strong>3.日期类型</strong></p><p>日期类型只有一种——DATE 类型，用来存储日期和时间信息，DATE类型的存储空间是7个字节，分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。</p><p><strong>4.布尔类型</strong></p><p>布尔类型也只有一种——BOOLEAN类型，其变量值可以是TRUE、FALSE 或NULL 中的一种。</p><h3 id="特殊数据类型"><a href="#特殊数据类型" class="headerlink" title="特殊数据类型"></a>特殊数据类型</h3><p><strong>1.%TYPE 类型</strong></p><p>使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，比如声明一个与 emp 表中job 列的数据类型完全相同的变量var_job：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">declare</span> var_job emp.job%<span class="keyword">type</span>;</span><br></pre></td></tr></table></figure><p></p><p>如果emp.job 列的数据类型为VARCHAR2(10)，那么变量var_job 的数据类型也是 VARCHAR2(10)。</p><p>比如，使用%type 类型的变量输出emp 表中编号为7369 的员工名称和职务信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">   var_ename scott.emp.ename%type;</span><br><span class="line">   var_job scott.emp.job%type;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">select</span> ename,job</span><br><span class="line">   <span class="keyword">into</span> var_ename,var_job</span><br><span class="line">   <span class="keyword">from</span> scott.emp <span class="keyword">where</span> empno=<span class="number">7369</span>;</span><br><span class="line">   dbms_output.put_line(var_ename||'的职务是'||var_job);</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">SMITH的职务是CLERK</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><div class="note danger"><p>由于into子句中的变量只能存储一个单独的值，所以要求select 子句只能返回一行数据。若SELECT子句返回多行数据，则代码运行后会返回错误信息。</p></div><p><strong>2.RECORD 类型</strong></p><p>RECORD 类型也称作“记录类型”，存储由多个列值组成的一行数据。语法结构如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">type record_type is record</span><br><span class="line">(</span><br><span class="line">var_member1 data_type [not null] [:=default_value],</span><br><span class="line">…</span><br><span class="line">var_membern data_type [not null] [:=default_value])</span><br></pre></td></tr></table></figure><p></p><p>比如，声明一个记录类型emp_type，然后使用该类型的变量存储emp表中的一条记录信息，并输出这条记录信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">   type emp_type is record</span><br><span class="line">   (</span><br><span class="line">     var_ename varchar2(20),</span><br><span class="line">     var_job varchar2(20),</span><br><span class="line">     var_sal number</span><br><span class="line">   );</span><br><span class="line">   empinfo emp_type;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">select</span> ename,job,sal</span><br><span class="line">   <span class="keyword">into</span> empinfo <span class="keyword">from</span> scott.emp</span><br><span class="line">   <span class="keyword">where</span> empno=<span class="number">7369</span>;</span><br><span class="line">   dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||'，工资是'||empinfo.var_sal);</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">雇员SMITH的职务是CLERK，工资是800</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><p><strong>3.%ROWTYPE 类型</strong></p><p>%ROWTYPE 类型的变量结合了%TYPE 类型和RECORD 类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据，语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">rowVar_name table_name%rowtype;</span><br></pre></td></tr></table></figure><p></p><p>使用%ROWTYPE改造上个RECORD类型的例子：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">   row_emp scott.emp%rowtype;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">select</span> * <span class="keyword">into</span> row_emp</span><br><span class="line">   <span class="keyword">from</span> scott.emp <span class="keyword">where</span> empno=<span class="number">7369</span>;</span><br><span class="line">   dbms_output.put_line('雇员'||row_emp.ename||'的职务是'||row_emp.job||'，工资是'||row_emp.sal);</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">雇员SMITH的职务是CLERK，工资是800</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h2 id="变量与常量"><a href="#变量与常量" class="headerlink" title="变量与常量"></a>变量与常量</h2><h3 id="定义变量"><a href="#定义变量" class="headerlink" title="定义变量"></a>定义变量</h3><p>定义变量的格式如下所示：</p><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">&lt;变量名&gt; &lt;数据类型&gt; [(长度):=&lt;初始值&gt;];</span><br></pre></td></tr></table></figure><p></p><p>比如： 定义一个用于存储国家名称的可变字符串变量 var_countryname，该变量的最大长度 是50，并且该变量的初始值为“中国”：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">var_countryname varchar2(50):='中国';</span><br></pre></td></tr></table></figure><p></p><h3 id="定义常量"><a href="#定义常量" class="headerlink" title="定义常量"></a>定义常量</h3><p>定义常量的格式如下所示：</p><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">&lt;常量名&gt; constant &lt;数据类型&gt;:=&lt;常量值&gt;;</span><br></pre></td></tr></table></figure><p></p><p>定义一个常量con_day，用来存储一年的天数：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">con_day constant integer:=365;</span><br></pre></td></tr></table></figure><p></p><h2 id="流程控制"><a href="#流程控制" class="headerlink" title="流程控制"></a>流程控制</h2><h3 id="if…then语句"><a href="#if…then语句" class="headerlink" title="if…then语句"></a>if…then语句</h3><p>其语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">if &lt; condition_expression&gt; then</span><br><span class="line">   plsql_sentence</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">if</span>;</span><br></pre></td></tr></table></figure><p></p><p>如果 if 后面的条件表达式存在“并且”、“或者”、“非”等逻辑运算，则可以使用“and”、“or”、“not”等逻辑运算符。另外，如果要判断if后面的条件表达式的值为空值，则需要在条件表达式中使用“is”和“null”关键字。</p><h3 id="if…then…else语句"><a href="#if…then…else语句" class="headerlink" title="if…then…else语句"></a>if…then…else语句</h3><p>其语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">if &lt;condition_expression&gt; then</span><br><span class="line">   plsql_sentence1;</span><br><span class="line">else</span><br><span class="line">   plsql_sentence2;</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">if</span>;</span><br></pre></td></tr></table></figure><p></p><h3 id="if…then…elsif语句"><a href="#if…then…elsif语句" class="headerlink" title="if…then…elsif语句"></a>if…then…elsif语句</h3><p>其语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">if &lt; condition_expression1 &gt; then</span><br><span class="line">   plsql_sentence_1;</span><br><span class="line">elsif &lt; condition_expression2 &gt; then</span><br><span class="line">   plsql_sentence_2;</span><br><span class="line">   ...</span><br><span class="line">else</span><br><span class="line">   plsql_sentence_n;</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">if</span>;</span><br></pre></td></tr></table></figure><p></p><h3 id="case语句"><a href="#case语句" class="headerlink" title="case语句"></a>case语句</h3><p>其语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">case &lt; selector&gt;</span><br><span class="line">   when &lt;expression_1&gt; then </span><br><span class="line">      plsql_sentence_1;</span><br><span class="line">   when &lt;expression_2&gt; then </span><br><span class="line">      plsql_sentence_2;</span><br><span class="line">   ...</span><br><span class="line">   when &lt;expression_n&gt; then </span><br><span class="line">      plsql_sentence_n;</span><br><span class="line">   [else plsql_sentence;]</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">case</span>;</span><br></pre></td></tr></table></figure><p></p><h3 id="loop语句"><a href="#loop语句" class="headerlink" title="loop语句"></a>loop语句</h3><p>loop 语句会先执行一次循环体，然后再判断“exit when”关键字后面的条件表达式的值是true 还是false，如果是true，则程序会退出循环体，否则程序将再次执行循环体，这样就使得程序至少能够执行一次循环体，语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">loop</span><br><span class="line">   plsql_sentence;</span><br><span class="line">   exit when end_condition_ exp;</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">loop</span>;</span><br></pre></td></tr></table></figure><p></p><p>比如计算1到100自然数的和：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">    sum_i int:=0;</span><br><span class="line">    i int:=0;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">loop</span></span><br><span class="line">    i:=i+<span class="number">1</span>;</span><br><span class="line">    sum_i:=sum_i+i;</span><br><span class="line">   exit when i&gt;=100;</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">loop</span>;</span><br><span class="line">   dbms_output.put_line('自然数1到100的和为：'||sum_i);</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">自然数1到100的和为：5050</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h3 id="while语句"><a href="#while语句" class="headerlink" title="while语句"></a>while语句</h3><p>语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">while condition_expression loop</span><br><span class="line">   plsql_sentence;</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">loop</span>;</span><br></pre></td></tr></table></figure><p></p><p>使用 while 语句求得前100 个自然数的和：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">     sum_i int:=0;</span><br><span class="line">     i int:=0;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">while</span> i&lt;=<span class="number">99</span> <span class="keyword">loop</span></span><br><span class="line">     i:=i+<span class="number">1</span>;</span><br><span class="line">     sum_i:=sum_i+i;</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">loop</span>;</span><br><span class="line">   dbms_output.put_line('自然数1到100的和为：'||sum_i);</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">自然数1到100的和为：5050</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h3 id="for语句"><a href="#for语句" class="headerlink" title="for语句"></a>for语句</h3><p>语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">for variable_ counter_name in [reverse] lower_limit..upper_limit loop</span><br><span class="line">   plsql_sentence;</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">loop</span>;</span><br></pre></td></tr></table></figure><p></p><ul><li><p><code>variable_ counter_name</code>：表示一个变量，通常为整数类型，用来作为计数器。默认情况下计数器的值会循环递增，当在循环中使用<code>reverse</code>关键字时，计数器的值会随循环递减。</p></li><li><p><code>lower_limit</code>：计数器的下限值，当计数器的值小于下限值时，程序终止for循环。</p></li><li><p><code>upper_limit</code>：计数器的上限值，当计数器的值大于上限值时，程序终止for循环。</p></li></ul><p>比如，使用 for 语句求得前100 个自然数中奇数之和，</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">    i int:=0;</span><br><span class="line">    sum_i int:=0;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> <span class="keyword">reverse</span> <span class="number">1.</span><span class="number">.100</span> <span class="keyword">loop</span></span><br><span class="line">      <span class="keyword">if</span> <span class="keyword">mod</span>(i,<span class="number">2</span>)!=<span class="number">0</span> <span class="keyword">then</span></span><br><span class="line">        sum_i:=sum_i+i;</span><br><span class="line">      <span class="keyword">end</span> <span class="keyword">if</span>;</span><br><span class="line">    <span class="keyword">end</span> <span class="keyword">loop</span>;</span><br><span class="line">    dbms_output.put_line('自然数1到100的奇数和为：'||sum_i);</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">自然数1到100的奇数和为：2500</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><p>在上面的for 语句中，由于使用了关键字“reverse”，表示计数器i 的值为递减状态，即i 的初始值为100，随着每次递减1，最后一次for 循环时i 的值变为1。如果在for 语句中不使用关键字“reverse”，则表示计数器i 的值为递增状态，即i 的初始值为1。</p><h2 id="游标"><a href="#游标" class="headerlink" title="游标"></a>游标</h2><p>游标分为显式游标和隐式游标。</p><h3 id="显式游标"><a href="#显式游标" class="headerlink" title="显式游标"></a>显式游标</h3><p>显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集，使用它处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤。</p><p><strong>1.声明游标</strong></p><p>声明游标主要包括游标名称和为游标提供结果集的 SELECT 语句。语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">cursor cur_name[(input_parameter1[,input_parameter2]…)]</span><br><span class="line">   [return ret_type]</span><br><span class="line">is select_ sentence;</span><br></pre></td></tr></table></figure><p></p><ul><li><p><code>cur_name</code>：表示所声明的游标名称。</p></li><li><p><code>ret_type</code>：表示执行游标操作后的返回值类型，这是一个可选项。</p></li><li><p><code>select_ sentence</code>：游标所使用的SELECT 语句，它为游标的反复读取提供了结果集。</p></li><li><p><code>input_parameter1</code>：作为游标的“输入参数”，可以有多个，这是一个可选项。</p></li></ul><p>比如声明一个游标，用来读取emp 表中职务为销售员（SALESMAN）的雇员信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; declare</span><br><span class="line">   cursor cur_emp(var_job in varchar2:='SALESMAN')</span><br><span class="line">   is <span class="keyword">select</span> empno,ename,sal</span><br><span class="line">      <span class="keyword">from</span> emp</span><br><span class="line">      <span class="keyword">where</span> job=var_job;</span><br></pre></td></tr></table></figure><p></p><div class="note danger"><p>输入参数var_job类型为varchar2，但不可以指定长度，如：varchar2(10)，否则程序报错。</p></div><p><strong>2.打开游标</strong></p><p>打开游标的语法格式如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">open cur_name[(para_value1[,para_value2]…)];</span><br></pre></td></tr></table></figure><p></p><p>比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">open cur_emp('MANAGER');</span><br></pre></td></tr></table></figure><p></p><p>上面这条语句表示打开游标 cur_emp，然后给游标的“输入参数”赋值为“MANAGER”。当然这里可以省略“(‘MANAGER’)”，这样表示“输入参数”的值仍然使用其初始值（即SALESMAN）。</p><p><strong>3.读取游标</strong></p><p>当打开一个游标之后，就可以读取游标中的数据了其语法格式如下：</p><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">fetch cur_name into &#123;variable&#125;;</span><br></pre></td></tr></table></figure><p></p><ul><li><p><code>cur_name</code>：要读取的游标名称。</p></li><li><p><code>variable</code>：%RECORD类型或者%ROWTYPE类型变量。</p></li></ul><p><strong>4.关闭游标</strong></p><p>游标使用完毕后需要关闭，以释放系统资源，比如 SELECT 语句返回的结果集等。它的语法格式如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">close cur_name;</span><br></pre></td></tr></table></figure><p></p><h3 id="游标的属性"><a href="#游标的属性" class="headerlink" title="游标的属性"></a>游标的属性</h3><p>无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen 和%rowcount4个属性，通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息：</p><ol><li><p>%found：布尔型属性，如果SQL 语句至少影响到一行数据，则该属性为true，否则为fasle。</p></li><li><p>%notfound：布尔型属性，与%found 属性的功能相反。</p></li><li><p>%rowcount：数字型属性，返回受SQL 语句影响的行数。</p></li><li><p>%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。</p></li></ol><p>下面举个使用显式游标的例子：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">   cursor cur_emp(var_job in varchar2:='SALESMAN')</span><br><span class="line">   is</span><br><span class="line">    <span class="keyword">select</span> * <span class="keyword">from</span> scott.emp <span class="keyword">where</span> job=var_job;</span><br><span class="line">   row_emp scott.emp%rowtype;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">open</span> cur_emp;</span><br><span class="line">   fetch cur_emp into row_emp;   //先让指针指向结果集中的第一行，并将值保存到row_emp中</span><br><span class="line">   while cur_emp%found loop</span><br><span class="line">     dbms_output.put_line('雇员'||row_emp.ename||'的编号是'||row_emp.empno||'，工资是：'||row_emp.sal);</span><br><span class="line">     fetch cur_emp into row_emp;  //让指针指向结果集中的下一行，并将值保存到row_emp中</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">loop</span>;</span><br><span class="line">   close cur_emp;</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">雇员ALLEN的编号是7499，工资是：1600</span><br><span class="line">雇员WARD的编号是7521，工资是：1250</span><br><span class="line">雇员MARTIN的编号是7654，工资是：1250</span><br><span class="line">雇员TURNER的编号是7844，工资是：1500</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h3 id="隐式游标"><a href="#隐式游标" class="headerlink" title="隐式游标"></a>隐式游标</h3><p>在执行一个 SQL 语句时，Oracle 会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操纵语句（如UPDATE、DELETE 语句）的执行结果，当然特殊情况下，也可以处理SELECT 语句的查询结果。由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称——sql。</p><p>在实际的 PL/SQL 编程中，经常使用隐式游标来判断更新数据行或删除数据行的情况。</p><p>比如把scott.emp表中销售员（即SALESMAN）的工资上调20%，然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; begin</span><br><span class="line">    <span class="keyword">update</span> scott.emp</span><br><span class="line">    <span class="keyword">set</span> sal=sal*<span class="number">1.2</span> <span class="keyword">where</span> job=<span class="string">'SALESMAN'</span>;</span><br><span class="line">   if sql%notfound then</span><br><span class="line">    dbms_output.put_line('没有雇员需要上调工资');</span><br><span class="line">   else</span><br><span class="line">    dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">if</span>;</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">有4个雇员工资上调20%</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h3 id="通过for语句循环游标"><a href="#通过for语句循环游标" class="headerlink" title="通过for语句循环游标"></a>通过for语句循环游标</h3><p>在使用隐式游标或显式游标处理具有多行数据的结果集时，用户可以配合for语句来完成。在使用for语句遍历游标中的数据时，可以把它的计时器看做一个自动的RECORD类型的变量。</p><p>比如使用for遍历一个隐式游标：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; begin</span><br><span class="line">   for emp_record in (<span class="keyword">select</span> * <span class="keyword">from</span> scott.emp <span class="keyword">where</span> job=<span class="string">'SALESMAN'</span>) <span class="keyword">loop</span></span><br><span class="line">     dbms_output.put_line(<span class="string">'雇员编号：'</span>||emp_record.empno||<span class="string">'，雇员姓名：'</span>||emp_record.ename||<span class="string">'，工资为：'</span>||emp_record);</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">loop</span>;</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">雇员编号：7499，雇员姓名：ALLEN，工资为：1920</span><br><span class="line">雇员编号：7521，雇员姓名：WARD，工资为：1500</span><br><span class="line">雇员编号：7654，雇员姓名：MARTIN，工资为：1500</span><br><span class="line">雇员编号：7844，雇员姓名：TURNER，工资为：1800</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h2 id="异常"><a href="#异常" class="headerlink" title="异常"></a>异常</h2><p>在编写PL/SQL程序时，避免不了会发生一些异常。Oracle 系统异常分为预定义异常和自定义异常。</p><h3 id="预定义异常"><a href="#预定义异常" class="headerlink" title="预定义异常"></a>预定义异常</h3><p>Oracle系统常见的预定义异常及其说明如下表所示：</p><table><tr><th>系统预定义异常</th><th>说明</th></tr><tr><td>ZERO_DIVIDE</td><td>除数为零时引发的异常</td></tr><tr><td>ACCESS_INTO_NULL</td><td>企图为某个未初始化对象的属性赋值</td></tr><tr><td>COLLECTION_IS_NULL</td><td>企图使用未初始化的集合元素</td></tr><tr><td>CURSOR_ALREADY_OPEN</td><td>企图再次打开一个已经打开过的游标，但在重新打开之前，游标未关闭</td></tr><tr><td>INVALID_CURSOR</td><td>执行一个非法的游标操作，例如，关闭一个未打开的游标</td></tr><tr><td>INVALID_NUMBER</td><td>企图将一个字符串转换成一个无效的数字而失败</td></tr><tr><td>LOGIN_DENIED</td><td>企图使用无效的用户名或密码连接数据库</td></tr><tr><td>NO_DATA_FOUND</td><td>SELECT INTO 语句没有返回数据</td></tr><tr><td>ROWTYPE_MISMATCH</td><td>主游标变量与PL/SQL 游标变量的返回类型不兼容</td></tr><tr><td>SELF_IS_NULL</td><td>使用对象类型时，使用空对象调用其方法</td></tr><tr><td>SUBSCRIPT_BEYOND_COUNT</td><td>元素下标超过嵌套表或VARRAY 的最大值</td></tr><tr><td>SUBSCRIPT_OUTSIDE_LIMIT</td><td>企图使用非法索引号引用嵌套表或VARRAY 中的元素</td></tr><tr><td>SYS_INVALID_ROWID</td><td>字符串向ROWID 转换时的错误，因为该字符串不是一个有效的ROWID 值</td></tr><tr><td>TIMEOUT_ON_RESOURCE</td><td>Oracle 在等待资源时超时</td></tr><tr><td>TOO_MANY_ROWS</td><td>执行SELECT INTO 语句时，结果集超过一行引发的异常</td></tr></table><p>比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">   var_empno number;</span><br><span class="line">   var_ename varchar2(50);</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">select</span> empno,ename <span class="keyword">into</span> var_empno,var_ename</span><br><span class="line">   <span class="keyword">from</span> scott.emp <span class="keyword">where</span> deptno=<span class="number">10</span>;</span><br><span class="line">   if sql%found then</span><br><span class="line">    dbms_output.put_line('雇员编号：'||var_empno);</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">if</span>;</span><br><span class="line">  exception</span><br><span class="line">   when too_many_rows then</span><br><span class="line">    dbms_output.put_line('返回记录超过一行');</span><br><span class="line">   when no_data_found then</span><br><span class="line">    dbms_output.put_line('无数据');</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">返回记录超过一行</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><h3 id="自定义异常"><a href="#自定义异常" class="headerlink" title="自定义异常"></a>自定义异常</h3><p>Oracle的自定义异常就可以分为错误编号异常和业务逻辑异常两种。</p><p><strong>1.错误编号异常</strong></p><p>错误编号异常是指在Oracle系统发生错误时，系统会显示错误编号和相关描述信息的异常，比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; insert into scott.dept values(10,'开发一部','福州');</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> scott.dept <span class="keyword">values</span>(<span class="number">10</span>,<span class="string">'开发一部'</span>,<span class="string">'福州'</span>)</span><br><span class="line">*</span><br><span class="line">第 <span class="number">1</span> 行出现错误:</span><br><span class="line">ORA<span class="number">-00001</span>: 违反唯一约束条件 (SCOTT.PK_DEPT)</span><br></pre></td></tr></table></figure><p></p><p>对于这种异常，首先在PL/SQL块的声明部分（DECLARE 部分）使用<code>EXCEPTION</code>类型定义一个异常变量名，然后使用语句<code>PRAGMA EXCEPTION_INIT</code>为“错误编号”关联这个异常变量名，接下来就可以像对待系统预定义异常一样处理了。比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">   primary_iterant exception;</span><br><span class="line">   <span class="keyword">pragma</span> exception_init(primary_iterant,<span class="number">-00001</span>);</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">   <span class="keyword">insert</span> <span class="keyword">into</span> scott.dept <span class="keyword">values</span>(<span class="number">10</span>,<span class="string">'开发一部'</span>,<span class="string">'福州'</span>);</span><br><span class="line"> exception</span><br><span class="line">   when primary_iterant then</span><br><span class="line">     dbms_output.put_line('主键重复！');</span><br><span class="line"> <span class="keyword">end</span>;</span><br><span class="line"> /</span><br><span class="line">主键重复！</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><p><strong>2.业务异常</strong></p><p>程序开发人员可以根据具体的业务逻辑规则自定义一个异常。业务逻辑异常是Oracle系统本身无法知道的，这样就需要有一个引发异常的机制，引发业务逻辑异常通常使用<code>RAISE</code> 语句来实现。</p><p>比如，自定义一个异常变量，在向dept表中插入数据时，若判断loc字段的值为null，则使用raise语句引发异常：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set serveroutput on</span><br><span class="line">SQL&gt; declare</span><br><span class="line">    null_exception exception;</span><br><span class="line">    dept_row scott.dept%rowtype;</span><br><span class="line">  <span class="keyword">begin</span></span><br><span class="line">    dept_row.deptno:=<span class="number">66</span>;</span><br><span class="line">    dept_row.dname:='开发二部';</span><br><span class="line">    <span class="keyword">insert</span> <span class="keyword">into</span> scott.dept</span><br><span class="line">    <span class="keyword">values</span>(dept_row.deptno,dept_row.dname,dept_row.loc);</span><br><span class="line">    if dept_row.loc is null then</span><br><span class="line">      raise null_exception;</span><br><span class="line">    <span class="keyword">end</span> <span class="keyword">if</span>;</span><br><span class="line">  exception</span><br><span class="line">    when null_exception then</span><br><span class="line">       dbms_output.put_line('loc字段不能为空！');</span><br><span class="line">    <span class="keyword">rollback</span>;</span><br><span class="line">  <span class="keyword">end</span>;</span><br><span class="line">  /</span><br><span class="line">loc字段不能为空！</span><br><span class="line"></span><br><span class="line">PL/SQL 过程已成功完成。</span><br></pre></td></tr></table></figure><p></p><script>$(".post-body a").not(".thispage").addClass("ignore-href").attr("target","_blank")</script></div><div></div><div><div style="padding:10px 0;margin:20px auto;width:90%;text-align:center;color:#878787"><div>请作者喝瓶肥宅水~</div><button id="rewardButton" style="margin-top:10px" disable="enable" onclick='var e=document.getElementById("QR");"none"===e.style.display?e.style.display="block":e.style.display="none"'><span style="height:46px;width:46px;line-height:46px;border-radius:50%;color:#fe5f55;font-weight:600;background:#ffd5be;border:none;box-shadow:0 4px 8px 0 rgba(255,213,190,.4)">￥</span></button><div id="QR" style="display:none"><div id="wechat" style="display:inline-block"><img id="wechat_qr" src="/img/wechat_pay.png" alt="MrBird WeChat Pay"></div><div id="alipay" style="display:inline-block"><img id="alipay_qr" src="/img/ali_pay.png" alt="MrBird Alipay"></div></div></div><style>#QR img{width:auto;margin:.8em 1em 0 1em}</style></div><div><ul class="post-copyright"><li class="post-copyright-author"><strong>本文作者：</strong> MrBird</li><li class="post-copyright-link"><strong>本文链接：</strong> <a href="http://mrbird.cc/Oracle-PL-SQL编程.html" title="Oracle PL/SQL编程">http://mrbird.cc/Oracle-PL-SQL编程.html</a></li><li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明出处！</li></ul></div><footer class="post-footer"><div class="post-tags" style="margin-bottom:1.3rem"><a href="/tags/DataBase/" rel="tag"># DataBase</a> <a href="/tags/Oracle/" rel="tag"># Oracle</a> <a href="/tags/Oracle-11g/" rel="tag"># Oracle 11g</a></div><div class="post-nav"><div class="post-nav-next post-nav-item"><a href="/Oracle-SQLPlus命令.html" rel="next" title="Oracle SQLPlus命令"><i class="fa fa-chevron-left"></i> Oracle SQLPlus命令</a></div><span class="post-nav-divider"></span><div class="post-nav-prev post-nav-item"><a href="/Oracle Stored procedure function.html" rel="prev" title="Oracle存储过程与函数">Oracle存储过程与函数 <i class="fa fa-chevron-right"></i></a></div></div></footer></article><hr><div id="container"></div><div class="post-spread"><div id="comment-div"></div><style>.valine .vlist{margin-bottom:3rem}.valine .vwrap .vcontrol .col.col-60{text-align:left}.valine .vlist .vcard .vhead,.valine .vlist .vcard section .vfooter{text-align:left}.valine .vlist .vcard section{padding-bottom:.5rem!important}.vname{color:#42b983!important}.valine .vinfo .col{text-align:left;margin-left:-27rem}div#comment-div{margin-bottom:-8rem}.valine .vlist .vcard .vcontent .code,.valine .vlist .vcard .vcontent code,.valine .vlist .vcard .vcontent pre{background:#fbfbfb}.valine .vlist .vcard .vcontent a{color:#42b983}.valine .vlist .vcard .vimg{border-radius:3px}.valine .vbtn{border-radius:2px;padding:.3rem 1.25rem}.valine .vbtn:active,.valine .vbtn:hover{color:#42b983;border-color:#42b983;background-color:#fff}.valine .vwrap .vheader .vinput:focus{border-bottom-color:#42b983}.valine .vlist .vcard .vcontent.expand:before{background:-webkit-gradient(linear,left top,left bottom,from(hsla(0,0%,100%,0)),to(hsla(0,0%,100%,.2)));background:linear-gradient(180deg,hsla(0,0%,100%,0),hsla(0,0%,100%,.2))}.valine .vlist .vcard .vcontent.expand:after{content:"点击展开";font-size:.4rem;text-align:right;left:-1rem;background:hsla(0,0%,100%,.2)}.valine .vlist .vcard section .vfooter .vat{color:#b3b3b3}.valine .vlist .vcard section .vfooter .vat:hover{color:#42b983}.vcontent img{margin:0}.valine .info{display:none}</style><script type="text/javascript" src="/js/av.min.js"></script><script type="text/javascript" src="/js/Valine.min.js"></script><script type="text/javascript" src="/js/activate-power-mode.js"></script><script>POWERMODE.colorful=!0,POWERMODE.shake=!1,document.body.addEventListener("input",POWERMODE),new Valine({el:"#comment-div",notify:!1,verify:!0,appId:"SMcDFP1bN1jgb9Lo8JmtICHm-gzGzoHsz",appKey:"dH4nrUrt3V5XiJiI6Qyejnbi",placeholder:"",path:window.location.pathname,avatar:"monsterid",guest_info:["nick","mail","link"]});var chicken='<a href="#"><img src="https://image.uisdc.com/wp-content/uploads/2018/06/uisdc-chat-chicken.gif" class="checken"></a>';$("#comment-div").prepend(chicken)</script></div></div><script>var $bqinline=$("img[alt='bq-inline']");$bqinline.css({width:"2.3rem",height:"2.3rem",display:"inline","vertical-align":"text-bottom"})</script></div><div class="comments" id="comments"></div></div><aside id="sidebar" class="sidebar" onselectstart="return!1"><div class="sidebar-inner"><ul class="sidebar-nav motion-element"><li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">Contents</li><li class="sidebar-nav-overview" data-target="site-overview">Site Preview</li></ul><section class="site-overview sidebar-panel"><div class="sidebar-sticky sticky"><div itemscope itemtype="http://schema.org/Person"><div class="author__avatar"><img src="/images/blogImage.jpg" class="author__avatar" alt="MrBird" itemprop="image"></div><div class="author__content"><h3 class="author__name" itemprop="name">MrBird's Blog</h3><p class="author__bio" itemprop="description">A simple blog, code repository, just keep blogging</p></div><div class="author__urls-wrapper"><button class="btn btn--inverse">Follow</button><ul class="author__urls social-icons"><li><a href="http://map.baidu.com/?newmap=1&s=s%26wd%3D%E7%A6%8F%E5%B7%9E%E5%B8%82%26c%3D300&from=alamap&tpl=mapcity" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-map-marker" aria-hidden="true"></i>&nbsp;&nbsp;FuZhou,CN</a></li><li><a href="https://love.mrbird.cc" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-diamond" aria-hidden="true"></i>&nbsp;&nbsp;Love</a></li><li><a href="https://cloud.mrbird.cn" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-chain" aria-hidden="true"></i>&nbsp;&nbsp;FEBS</a></li><li><a href="/atom.xml" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-rss" aria-hidden="true"></i>&nbsp;&nbsp;RSS</a></li><li><a href="https://gitee.com/mrbirdd" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-codepen" aria-hidden="true"></i>&nbsp;&nbsp;Gitee</a></li><li><a href="https://github.com/wuyouzhuguli" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-github-alt" aria-hidden="true"></i>&nbsp;&nbsp;GitHub</a></li><li><a href="javascript:;" class="popup-trigger"><i class="fa fa-fw fa-search" aria-hidden="true"></i>&nbsp;&nbsp;Search</a></li></ul></div></div></div><script>var $urls=$(".author__urls").find("a");$urls.each(function(){var o=$(this);o.mouseenter(function(){o.css({color:"#414547"})}),o.mouseleave(function(){o.css({color:""})})})</script></section><section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active"><div class="post-toc"><div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#数据类型"><span class="nav-number">1.</span> <span class="nav-text">数据类型</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#基本数据类型"><span class="nav-number">1.1.</span> <span class="nav-text">基本数据类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#特殊数据类型"><span class="nav-number">1.2.</span> <span class="nav-text">特殊数据类型</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#变量与常量"><span class="nav-number">2.</span> <span class="nav-text">变量与常量</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#定义变量"><span class="nav-number">2.1.</span> <span class="nav-text">定义变量</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#定义常量"><span class="nav-number">2.2.</span> <span class="nav-text">定义常量</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#流程控制"><span class="nav-number">3.</span> <span class="nav-text">流程控制</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#if…then语句"><span class="nav-number">3.1.</span> <span class="nav-text">if…then语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#if…then…else语句"><span class="nav-number">3.2.</span> <span class="nav-text">if…then…else语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#if…then…elsif语句"><span class="nav-number">3.3.</span> <span class="nav-text">if…then…elsif语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#case语句"><span class="nav-number">3.4.</span> <span class="nav-text">case语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#loop语句"><span class="nav-number">3.5.</span> <span class="nav-text">loop语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#while语句"><span class="nav-number">3.6.</span> <span class="nav-text">while语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#for语句"><span class="nav-number">3.7.</span> <span class="nav-text">for语句</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#游标"><span class="nav-number">4.</span> <span class="nav-text">游标</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#显式游标"><span class="nav-number">4.1.</span> <span class="nav-text">显式游标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#游标的属性"><span class="nav-number">4.2.</span> <span class="nav-text">游标的属性</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#隐式游标"><span class="nav-number">4.3.</span> <span class="nav-text">隐式游标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#通过for语句循环游标"><span class="nav-number">4.4.</span> <span class="nav-text">通过for语句循环游标</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#异常"><span class="nav-number">5.</span> <span class="nav-text">异常</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#预定义异常"><span class="nav-number">5.1.</span> <span class="nav-text">预定义异常</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#自定义异常"><span class="nav-number">5.2.</span> <span class="nav-text">自定义异常</span></a></li></ol></li></ol></div></div></section></div></aside></div></main><footer id="footer" class="footer" onselectstart="return!1"><div class="footer-inner"><div class="copyright">&copy; 2016 - <span itemprop="copyrightYear">2019</span>&nbsp;&nbsp; <span class="author" itemprop="copyrightHolder">MrBird</span>&nbsp;&nbsp;|<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>&nbsp;&nbsp;UV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_uv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span> &nbsp;&nbsp;PV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_pv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span></div></div></footer><div class="back-to-top"><span style="font-family:'Source Sans Pro','Helvetica Neue',Arial,sans-serif;font-size:1.2em;font-weight:600">TOP</span></div></div><script type="text/javascript">"[object Function]"!==Object.prototype.toString.call(window.Promise)&&(window.Promise=null)</script><script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script><script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script><script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script><script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script><script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script><script type="text/javascript" src="/js/src/utils.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/motion.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/post-details.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.1"></script><script type="text/javascript">function proceedsearch(){$("body").append('<div class="search-popup-overlay local-search-pop-overlay"></div>').css("overflow","hidden"),$(".search-popup-overlay").click(onPopupClose),$(".popup").toggle();var t=$("#local-search-input");t.attr("autocapitalize","none"),t.attr("autocorrect","off"),t.focus()}var isfetched=!1,isXml=!0,search_path="search.xml";0===search_path.length?search_path="search.xml":search_path.endsWith("json")&&(isXml=!1);var path="/"+search_path,onPopupClose=function(t){$(".popup").hide(),$("#local-search-input").val(""),$(".search-result-list").remove(),$("#no-result").remove(),$(".local-search-pop-overlay").remove(),$("body").css("overflow","")},searchFunc=function(t,e,o){"use strict";$("body").append('<div class="search-popup-overlay local-search-pop-overlay"><div id="search-loading-icon"><i class="fa fa-spinner fa-pulse fa-2x fa-fw"></i></div></div>').css("overflow","hidden"),$("#search-loading-icon").css("margin","20% auto 0 auto").css("text-align","center"),$.ajax({url:t,dataType:isXml?"xml":"json",async:!0,success:function(t){isfetched=!0,$(".popup").detach().appendTo(".header-inner");var n=isXml?$("entry",t).map(function(){return{title:$("title",this).text(),content:$("content",this).text(),url:$("url",this).text()}}).get():t,r=document.getElementById(e),s=document.getElementById(o),a=function(){var t=r.value.trim().toLowerCase(),e=t.split(/[\s\-]+/);e.length>1&&e.push(t);var o=[];if(t.length>0&&n.forEach(function(n){function r(e,o,n,r){for(var s=r[r.length-1],a=s.position,i=s.word,l=[],h=0;a+i.length<=n&&0!=r.length;){i===t&&h++,l.push({position:a,length:i.length});var p=a+i.length;for(r.pop();0!=r.length&&(s=r[r.length-1],a=s.position,i=s.word,p>a);)r.pop()}return c+=h,{hits:l,start:o,end:n,searchTextCount:h}}function s(t,e){var o="",n=e.start;return e.hits.forEach(function(e){o+=t.substring(n,e.position);var r=e.position+e.length;o+='<b class="search-keyword">'+t.substring(e.position,r)+"</b>",n=r}),o+=t.substring(n,e.end)}var a=!1,i=0,c=0,l=n.title.trim(),h=l.toLowerCase(),p=n.content.trim().replace(/<[^>]+>/g,""),u=p.toLowerCase(),f=decodeURIComponent(n.url),d=[],g=[];if(""!=l&&(e.forEach(function(t){function e(t,e,o){var n=t.length;if(0===n)return[];var r=0,s=[],a=[];for(o||(e=e.toLowerCase(),t=t.toLowerCase());(s=e.indexOf(t,r))>-1;)a.push({position:s,word:t}),r=s+n;return a}d=d.concat(e(t,h,!1)),g=g.concat(e(t,u,!1))}),(d.length>0||g.length>0)&&(a=!0,i=d.length+g.length)),a){[d,g].forEach(function(t){t.sort(function(t,e){return e.position!==t.position?e.position-t.position:t.word.length-e.word.length})});var v=[];0!=d.length&&v.push(r(l,0,l.length,d));for(var C=[];0!=g.length;){var $=g[g.length-1],m=$.position,x=$.word,w=m-20,y=m+80;w<0&&(w=0),y<m+x.length&&(y=m+x.length),y>p.length&&(y=p.length),C.push(r(p,w,y,g))}C.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hits.length!==e.hits.length?e.hits.length-t.hits.length:t.start-e.start});var T=parseInt("1");T>=0&&(C=C.slice(0,T));var b="";b+=0!=v.length?"<li><a href='"+f+"' class='search-result-title'>"+s(l,v[0])+"</a>":"<li><a href='"+f+"' class='search-result-title'>"+l+"</a>",C.forEach(function(t){b+="<a href='"+f+'\'><p class="search-result">'+s(p,t)+"...</p></a>"}),b+="</li>",o.push({item:b,searchTextCount:c,hitCount:i,id:o.length})}}),1===e.length&&""===e[0])s.innerHTML='<div id="no-result"><i class="fa fa-search fa-5x" /></div>';else if(0===o.length)s.innerHTML='<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>';else{o.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hitCount!==e.hitCount?e.hitCount-t.hitCount:e.id-t.id});var a='<ul class="search-result-list">';o.forEach(function(t){a+=t.item}),a+="</ul>",s.innerHTML=a}};r.addEventListener("input",a),$(".local-search-pop-overlay").remove(),$("body").css("overflow",""),proceedsearch()}})};$(".popup-trigger").click(function(t){t.stopPropagation(),isfetched===!1?searchFunc(path,"local-search-input","local-search-result"):proceedsearch()}),$(".popup-btn-close").click(onPopupClose),$(".popup").click(function(t){t.stopPropagation()}),$(document).on("keyup",function(t){var e=27===t.which&&$(".search-popup").is(":visible");e&&onPopupClose()})</script></body><script>$(function(){$("a").not(".nav-link,.cloud-tie-join-count,.ignore-href,.jstree-anchor").addClass("animsition-link")});var burst1=new mojs.Burst({left:0,top:0,radius:{5:40},children:{shape:"circle",fill:["red","cyan","orange"],fillOpacity:.8,radiusX:3.5,radiusY:3.5}});document.addEventListener("click",function(a){null==a.target.href&&"footer"!=a.target.className&&"copyright"!=a.target.className&&"author__urls social-icons"!=a.target.className&&"author__avatar"!=a.target.className&&"sidebar sidebar-active"!=a.target.className&&burst1.tune({x:a.pageX,y:a.pageY}).generate().replay()})</script><script type="text/javascript" src="/js/message.js"></script></html><!-- rebuild by neat -->